// 一杆云枢 - MySQL版本后端服务器
require('dotenv').config();
const express = require('express');
const cors = require('cors');
const mysql = require('mysql2/promise');

const app = express();
const PORT = 3001;

// 数据库的连接配置
const dbConfig = {
  host: process.env.DB_HOST || 'localhost',
  port: process.env.DB_PORT || 3306,
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || 'ddydtx1126',
  database: process.env.DB_NAME || 'ballsystem',
  charset: 'utf8mb4',
  timezone: '+08:00'
};

let db = null;

// 中间件
// 允许的一些跨域域名
app.use(cors({
  origin: ['http://113.46.199.88:8080', 'http://113.46.199.88:8081', 'http://localhost:8080', 'http://localhost:8081', 'http://127.0.0.1:8081'],
  credentials: true,
  methods: ['GET', 'POST', 'PUT', 'DELETE', 'OPTIONS'],
  allowedHeaders: ['Content-Type', 'Authorization', 'X-Requested-With']
}));
app.use(express.json());

// 数据库连接函数
async function connectDatabase() {
  try {
    console.log('🔄 正在连接MySQL数据库...');
    db = await mysql.createConnection(dbConfig);
    console.log('✅ MySQL数据库连接成功');

    // 设置字符集
    await db.execute('SET NAMES utf8mb4');
    await db.execute('SET CHARACTER SET utf8mb4');
    await db.execute('SET character_set_connection=utf8mb4');

    // 测试连接
    await db.execute('SELECT 1');
    console.log('✅ 数据库连接测试通过');

    return true;
  } catch (error) {
    console.error('❌ MySQL连接失败:', error.message);
    console.log('🔄 将使用内存数据库模式');
    return false;
  }
}

// 内存数据库备用
const memoryDB = {
  users: [
    {
      id: 1,
      username: 'admin',
      phone: '13800000000',
      password: 'admin123',
      role: 'admin',
      status: 'active',
      balance: 0,
      play_time: 0,
      created_at: new Date()
    }
  ]
};

// 基础路由
app.get('/', (req, res) => {
  res.json({
    message: '一杆云枢 - 台球场馆管理系统后端API',
    version: '2.1.0',
    status: 'running',
    database: db ? 'MySQL' : '内存数据库'
  });
});

app.get('/api/health', (req, res) => {
  res.json({
    status: 'ok',
    timestamp: new Date().toISOString(),
    database: db ? 'MySQL连接正常' : '内存数据库模式'
  });
});

// 认证API
app.post('/api/auth/login', async (req, res) => {
  try {
    const { phone, password } = req.body;

    if (db) {
      // 使用MySQL查询 - 先查找用户
      const [rows] = await db.execute(
        'SELECT * FROM users WHERE phone = ?',
        [phone]
      );

      if (rows.length > 0) {
        const user = rows[0];
        // 简单密码验证（实际项目中应该使用bcrypt）
        if (password === 'admin123' || user.password === password) {
          res.json({
            success: true,
            message: '登录成功',
            data: {
              token: 'mysql-token-' + Date.now(),
              user: {
                id: user.id,
                username: user.username,
                phone: user.phone,
                role: user.role
              }
            }
          });
        } else {
          res.status(401).json({
            success: false,
            message: '密码错误'
          });
        }
      } else {
        res.status(401).json({
          success: false,
          message: '用户不存在'
        });
      }
    } else {
      // 使用内存数据库
      if (phone === '13800000000' && password === 'admin123') {
        res.json({
          success: true,
          message: '登录成功',
          data: {
            token: 'memory-token-' + Date.now(),
            user: {
              id: 1,
              username: 'admin',
              phone: '13800000000',
              role: 'admin'
            }
          }
        });
      } else {
        res.status(401).json({
          success: false,
          message: '手机号或密码错误'
        });
      }
    }
  } catch (error) {
    console.error('登录错误:', error);
    res.status(500).json({
      success: false,
      message: '登录服务异常'
    });
  }
});

// 用户API
app.get('/api/users', async (req, res) => {
  try {
    if (db) {
      // 使用MySQL查询
      const [rows] = await db.execute('SELECT id, username, phone, role, status, balance, play_time, created_at FROM users');
      res.json({
        success: true,
        data: {
          users: rows,
          total: rows.length
        }
      });
    } else {
      // 使用内存数据库
      res.json({
        success: true,
        data: {
          users: memoryDB.users.map(u => ({
            id: u.id,
            username: u.username,
            phone: u.phone,
            role: u.role,
            status: u.status,
            balance: u.balance,
            play_time: u.play_time,
            created_at: u.created_at
          })),
          total: memoryDB.users.length
        }
      });
    }
  } catch (error) {
    console.error('获取用户列表错误:', error);
    res.status(500).json({
      success: false,
      message: '获取用户列表失败: ' + error.message
    });
  }
});



// 台球桌API
app.get('/api/tables', async (req, res) => {
  try {
    if (db) {
      const [rows] = await db.execute('SELECT * FROM tables ORDER BY name');

      // 转换数据格式以匹配前端期望
      const tables = rows.map(table => ({
        id: table.id,
        name: table.name,
        type: table.type,
        hourly_rate: table.hourly_rate,
        status: table.status,
        description: table.description,
        created_at: table.created_at,
        updated_at: table.updated_at
      }));

      res.json({
        success: true,
        data: {
          tables: tables,
          total: tables.length
        }
      });
    } else {
      res.json({
        success: true,
        data: {
          tables: [],
          total: 0
        }
      });
    }
  } catch (error) {
    console.error('获取台球桌列表错误:', error);
    res.status(500).json({
      success: false,
      message: '获取台球桌列表失败: ' + error.message
    });
  }
});

// 订单API
app.get('/api/orders', async (req, res) => {
  try {
    if (db) {
      const [rows] = await db.execute(`
        SELECT o.*, u.username, u.real_name
        FROM orders o
        LEFT JOIN users u ON o.user_id = u.id
        ORDER BY o.created_at DESC
      `);

      // 格式化订单数据
      const orders = rows.map(order => ({
        id: order.id,
        user_id: order.user_id,
        username: order.username,
        real_name: order.real_name,
        table_id: order.table_id,
        table_name: order.table_name,
        table_type: order.table_type,
        booking_date: order.booking_date,
        start_time: order.start_time,
        end_time: order.end_time,
        duration: order.duration,
        total_cost: order.total_cost,
        phone: order.phone,
        status: order.status,
        created_at: order.created_at
      }));

      res.json({
        success: true,
        data: {
          orders: orders,
          total: orders.length
        }
      });
    } else {
      res.json({
        success: true,
        data: {
          orders: [],
          total: 0
        }
      });
    }
  } catch (error) {
    console.error('获取订单列表错误:', error);
    res.status(500).json({
      success: false,
      message: '获取订单列表失败: ' + error.message
    });
  }
});

// 公告API
app.get('/api/announcements', async (req, res) => {
  try {
    if (db) {
      const [rows] = await db.execute(`
        SELECT * FROM announcements
        WHERE status = 'published'
        ORDER BY priority DESC, publish_time DESC
      `);

      res.json({
        success: true,
        data: {
          announcements: rows,
          total: rows.length
        }
      });
    } else {
      res.json({
        success: true,
        data: {
          announcements: [],
          total: 0
        }
      });
    }
  } catch (error) {
    console.error('获取公告列表错误:', error);
    res.status(500).json({
      success: false,
      message: '获取公告列表失败: ' + error.message
    });
  }
});

// 获取最新公告 - 必须在 /:id 路由之前定义
app.get('/api/announcements/latest', async (req, res) => {
  try {
    const { limit = 6 } = req.query;
    const limitNum = parseInt(limit);

    if (isNaN(limitNum) || limitNum <= 0) {
      return res.status(400).json({
        success: false,
        message: '无效的限制数量'
      });
    }

    if (db) {
      const [rows] = await db.execute(`
        SELECT * FROM announcements
        WHERE status = 'published'
        ORDER BY priority DESC, publish_time DESC
        LIMIT ?
      `, [limitNum]);

      res.json({
        success: true,
        data: rows
      });
    } else {
      res.json({
        success: true,
        data: []
      });
    }
  } catch (error) {
    console.error('获取最新公告错误:', error);
    res.status(500).json({
      success: false,
      message: '获取最新公告失败: ' + error.message
    });
  }
});

// 获取单个公告详情
app.get('/api/announcements/:id', async (req, res) => {
  try {
    const announcementId = parseInt(req.params.id);

    if (isNaN(announcementId)) {
      return res.status(400).json({
        success: false,
        message: '无效的公告ID'
      });
    }

    if (db) {
      const [rows] = await db.execute(`
        SELECT * FROM announcements
        WHERE id = ? AND status = 'published'
      `, [announcementId]);

      if (rows.length === 0) {
        return res.status(404).json({
          success: false,
          message: '公告不存在'
        });
      }

      res.json({
        success: true,
        data: rows[0]
      });
    } else {
      res.status(404).json({
        success: false,
        message: '公告不存在'
      });
    }
  } catch (error) {
    console.error('获取公告详情错误:', error);
    res.status(500).json({
      success: false,
      message: '获取公告详情失败: ' + error.message
    });
  }
});

// 临时API：初始化轮播图公告数据
app.post('/api/init-carousel-announcements', async (req, res) => {
  try {
    if (db) {
      const announcements = [
        {
          id: 1,
          title: '会员充值优惠',
          content: '🎉 超值充值优惠活动火热进行中！\n\n💰 充值500元送100元，实际到账600元\n💰 充值1000元送300元，实际到账1300元\n💰 充值2000元送800元，实际到账2800元\n\n✨ 活动特色：\n• 充值即时到账，无需等待\n• 赠送金额可正常使用，无限制\n• 支持微信、支付宝、银行卡支付\n• 会员专享，享受更多优惠\n\n📅 活动时间：即日起至2025年12月31日\n📞 咨询热线：400-123-4567\n\n立即充值，享受超值优惠！更多优惠等你来享受！',
          type: 'promotion',
          priority: 10,
          status: 'published',
          publish_time: '2025-07-11 10:00:00',
          expire_time: '2025-12-31 23:59:59',
          created_by: 1
        },
        {
          id: 2,
          title: '新店开业大酬宾',
          content: '🎊 一杆云枢台球俱乐部盛大开业！\n\n🎁 开业特惠：\n• 全场台球桌8折优惠\n• 预订即送精美礼品一份\n• 新会员注册送50元代金券\n• 首次充值享受双倍积分\n\n🏆 店内设施：\n• 专业美式台球桌8张\n• 标准中式台球桌6张\n• 顶级斯诺克台球桌4张\n• 九球专用台球桌4张\n\n🌟 服务承诺：\n• 专业教练指导\n• 舒适休息环境\n• 免费茶水饮料\n• 24小时营业\n\n📍 地址：北京市朝阳区台球街123号\n🕐 营业时间：24小时营业\n\n欢迎新老客户光临体验！',
          type: 'event',
          priority: 9,
          status: 'published',
          publish_time: '2025-07-11 09:00:00',
          expire_time: '2025-08-31 23:59:59',
          created_by: 1
        },
        {
          id: 3,
          title: '斯诺克精英赛',
          content: '🏆 一杆云枢杯斯诺克精英赛即将开赛！\n\n🎯 比赛信息：\n• 比赛时间：每周六日 14:00-18:00\n• 比赛地点：一杆云枢台球俱乐部\n• 参赛费用：100元/人\n• 报名截止：每周五 18:00\n\n💰 丰厚奖金：\n🥇 冠军：1000元现金 + 精美奖杯\n🥈 亚军：500元现金 + 奖牌\n🥉 季军：300元现金 + 奖牌\n🏅 参与奖：所有参赛者均可获得纪念品\n\n📋 参赛要求：\n• 年满18周岁\n• 具备基本斯诺克技能\n• 遵守比赛规则和体育精神\n• 自备球杆（可租借）\n\n📞 报名方式：\n• 现场报名：前台登记\n• 电话报名：400-123-4567\n• 微信报名：扫描二维码\n\n周末斯诺克比赛火热进行中，展现你的球技，赢取丰厚奖金！',
          type: 'event',
          priority: 8,
          status: 'published',
          publish_time: '2025-07-11 08:00:00',
          expire_time: '2025-12-31 23:59:59',
          created_by: 1
        },
        {
          id: 4,
          title: '夜场特惠时光',
          content: '🌙 夜猫子专属福利来袭！\n\n⏰ 特惠时间：每日20:00-次日06:00\n💸 超值优惠：全场台球桌6折优惠\n\n🎮 夜场特色：\n• 安静舒适的环境\n• 专业的灯光设置\n• 免费夜宵小食\n• 24小时空调开放\n• 免费WiFi覆盖\n\n🍺 夜场服务：\n• 提供各类饮料和小食\n• 专业服务员全程服务\n• 可预订包间服务\n• 支持团体活动\n\n👥 适合人群：\n• 夜班工作者\n• 学生群体\n• 台球爱好者\n• 失眠人士\n\n💡 温馨提示：\n• 夜场需提前预约\n• 请保持安静，不影响他人\n• 禁止大声喧哗\n• 注意个人财物安全\n\n📱 预约电话：400-123-4567\n\n晚上8点后享受6折优惠，夜猫子专属福利，畅玩到深夜！',
          type: 'promotion',
          priority: 7,
          status: 'published',
          publish_time: '2025-07-11 07:00:00',
          expire_time: '2025-12-31 23:59:59',
          created_by: 1
        },
        {
          id: 5,
          title: '团体包场服务',
          content: '🎪 专业团体包场服务，打造完美聚会体验！\n\n🏢 适用场景：\n• 公司团建活动\n• 朋友聚会聚餐\n• 生日派对庆祝\n• 商务接待\n• 同学聚会\n\n📦 包场套餐：\n🎯 基础套餐（4小时）：\n• 包含4张台球桌\n• 免费茶水饮料\n• 专业服务员\n• 价格：800元\n\n🎯 豪华套餐（6小时）：\n• 包含6张台球桌\n• 精美茶点小食\n• 专业教练指导\n• KTV包间1小时\n• 价格：1500元\n\n🎯 至尊套餐（8小时）：\n• 全场包场\n• 豪华自助餐\n• 专业摄影服务\n• KTV包间3小时\n• 定制纪念品\n• 价格：3000元\n\n🎁 增值服务：\n• 生日蛋糕定制\n• 鲜花布置\n• 音响设备\n• 投影设备\n• 专业主持\n\n📞 预订热线：400-123-4567\n📧 邮箱：booking@yiganyunshu.com\n\n提前3天预订享受9折优惠！公司聚会、朋友聚餐首选，提供专业服务和精美茶点！',
          type: 'notice',
          priority: 6,
          status: 'published',
          publish_time: '2025-07-11 06:00:00',
          expire_time: '2025-12-31 23:59:59',
          created_by: 1
        }
      ];

      // 使用 INSERT ... ON DUPLICATE KEY UPDATE 来插入或更新数据
      for (const announcement of announcements) {
        await db.execute(`
          INSERT INTO announcements (id, title, content, type, priority, status, publish_time, expire_time, created_by, created_at, updated_at)
          VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())
          ON DUPLICATE KEY UPDATE
          title = VALUES(title),
          content = VALUES(content),
          type = VALUES(type),
          priority = VALUES(priority),
          status = VALUES(status),
          publish_time = VALUES(publish_time),
          expire_time = VALUES(expire_time),
          updated_at = NOW()
        `, [
          announcement.id,
          announcement.title,
          announcement.content,
          announcement.type,
          announcement.priority,
          announcement.status,
          announcement.publish_time,
          announcement.expire_time,
          announcement.created_by
        ]);
      }

      res.json({
        success: true,
        message: '轮播图公告数据初始化成功',
        data: { count: announcements.length }
      });
    } else {
      res.status(500).json({
        success: false,
        message: '数据库未连接'
      });
    }
  } catch (error) {
    console.error('初始化轮播图公告数据错误:', error);
    res.status(500).json({
      success: false,
      message: '初始化轮播图公告数据失败: ' + error.message
    });
  }
});

// 充值记录API
app.get('/api/recharge-records', async (req, res) => {
  try {
    if (db) {
      const [rows] = await db.execute(`
        SELECT r.*, u.username, u.real_name
        FROM recharge_records r
        LEFT JOIN users u ON r.user_id = u.id
        ORDER BY r.created_at DESC
      `);

      res.json({
        success: true,
        data: {
          records: rows,
          total: rows.length
        }
      });
    } else {
      res.json({
        success: true,
        data: {
          records: [],
          total: 0
        }
      });
    }
  } catch (error) {
    console.error('获取充值记录错误:', error);
    res.status(500).json({
      success: false,
      message: '获取充值记录失败: ' + error.message
    });
  }
});

// 管理员用户API
app.get('/api/admin/users', async (req, res) => {
  try {
    const { page = 1, pageSize = 10, role, status, search } = req.query;

    if (db) {
      let query = 'SELECT id, username, phone, email, real_name, role, status, balance, play_time, avatar, created_at, updated_at FROM users';
      let countQuery = 'SELECT COUNT(*) as total FROM users';
      let params = [];
      let conditions = [];

      // 添加筛选条件
      if (role) {
        conditions.push('role = ?');
        params.push(role);
      }
      if (status) {
        conditions.push('status = ?');
        params.push(status);
      }
      if (search) {
        conditions.push('(username LIKE ? OR phone LIKE ? OR real_name LIKE ?)');
        params.push(`%${search}%`, `%${search}%`, `%${search}%`);
      }

      if (conditions.length > 0) {
        const whereClause = ' WHERE ' + conditions.join(' AND ');
        query += whereClause;
        countQuery += whereClause;
      }

      // 添加分页
      const offset = (page - 1) * pageSize;
      query += ` ORDER BY created_at DESC LIMIT ${pageSize} OFFSET ${offset}`;

      const [users] = await db.execute(query, params);
      const [countResult] = await db.execute(countQuery, params);

      res.json({
        success: true,
        data: {
          users: users,
          total: countResult[0].total,
          page: parseInt(page),
          pageSize: parseInt(pageSize)
        }
      });
    } else {
      // 使用内存数据库
      let filteredUsers = memoryDB.users;

      if (role) {
        filteredUsers = filteredUsers.filter(u => u.role === role);
      }
      if (status) {
        filteredUsers = filteredUsers.filter(u => u.status === status);
      }
      if (search) {
        filteredUsers = filteredUsers.filter(u =>
          u.username.includes(search) ||
          u.phone.includes(search) ||
          (u.real_name && u.real_name.includes(search))
        );
      }

      const total = filteredUsers.length;
      const offset = (page - 1) * pageSize;
      const paginatedUsers = filteredUsers.slice(offset, offset + parseInt(pageSize));

      res.json({
        success: true,
        data: {
          users: paginatedUsers.map(u => ({
            id: u.id,
            username: u.username,
            phone: u.phone,
            email: u.email || '',
            real_name: u.real_name || '',
            role: u.role,
            status: u.status,
            balance: u.balance,
            play_time: u.play_time,
            avatar: u.avatar || '',
            created_at: u.created_at,
            updated_at: u.updated_at || u.created_at
          })),
          total: total,
          page: parseInt(page),
          pageSize: parseInt(pageSize)
        }
      });
    }
  } catch (error) {
    console.error('获取用户列表错误:', error);
    res.status(500).json({
      success: false,
      message: '获取用户列表失败: ' + error.message
    });
  }
});

// 用户统计API
app.get('/api/admin/users/stats', async (req, res) => {
  try {
    if (db) {
      const [totalResult] = await db.execute('SELECT COUNT(*) as total FROM users');
      const [activeResult] = await db.execute('SELECT COUNT(*) as active FROM users WHERE status = "active"');
      const [todayResult] = await db.execute('SELECT COUNT(*) as today FROM users WHERE DATE(created_at) = CURDATE()');
      const [roleStats] = await db.execute('SELECT role, COUNT(*) as count FROM users GROUP BY role');

      res.json({
        success: true,
        data: {
          totalUsers: totalResult[0].total,
          activeUsers: activeResult[0].active,
          newUsersToday: todayResult[0].today,
          roleStats: roleStats.reduce((acc, item) => {
            acc[item.role] = item.count;
            return acc;
          }, {})
        }
      });
    } else {
      const totalUsers = memoryDB.users.length;
      const activeUsers = memoryDB.users.filter(u => u.status === 'active').length;
      const today = new Date().toDateString();
      const newUsersToday = memoryDB.users.filter(u =>
        new Date(u.created_at).toDateString() === today
      ).length;

      res.json({
        success: true,
        data: {
          totalUsers,
          activeUsers,
          newUsersToday,
          roleStats: {
            admin: memoryDB.users.filter(u => u.role === 'admin').length,
            user: memoryDB.users.filter(u => u.role === 'user').length
          }
        }
      });
    }
  } catch (error) {
    console.error('获取用户统计错误:', error);
    res.status(500).json({
      success: false,
      message: '获取用户统计失败: ' + error.message
    });
  }
});

// 统计API
app.get('/api/stats/overview', async (req, res) => {
  try {
    let stats = {
      totalUsers: 0,
      totalOrders: 0,
      totalRevenue: 0,
      activeTables: 0
    };

    if (db) {
      // 从MySQL获取统计数据
      const [userCount] = await db.execute('SELECT COUNT(*) as count FROM users');
      const [orderCount] = await db.execute('SELECT COUNT(*) as count FROM orders');
      const [revenueSum] = await db.execute('SELECT COALESCE(SUM(total_cost), 0) as total FROM orders WHERE status = "completed"');
      const [activeTableCount] = await db.execute('SELECT COUNT(*) as count FROM tables WHERE status = "occupied"');

      stats = {
        totalUsers: userCount[0].count,
        totalOrders: orderCount[0].count,
        totalRevenue: revenueSum[0].total,
        activeTables: activeTableCount[0].count
      };
    } else {
      stats.totalUsers = memoryDB.users.length;
    }

    res.json({
      success: true,
      data: stats
    });
  } catch (error) {
    console.error('获取统计数据错误:', error);
    res.status(500).json({
      success: false,
      message: '获取统计数据失败: ' + error.message
    });
  }
});

// 管理员订单API
app.get('/api/admin/orders', async (req, res) => {
  try {
    const { page = 1, pageSize = 10, status, date_from, date_to } = req.query;

    if (db) {
      let query = `
        SELECT o.*, u.username, u.real_name, t.name as table_name
        FROM orders o
        LEFT JOIN users u ON o.user_id = u.id
        LEFT JOIN tables t ON o.table_id = t.id
      `;
      let countQuery = 'SELECT COUNT(*) as total FROM orders o';
      let params = [];
      let conditions = [];

      // 添加筛选条件
      if (status) {
        conditions.push('o.status = ?');
        params.push(status);
      }
      if (date_from) {
        conditions.push('DATE(o.booking_date) >= ?');
        params.push(date_from);
      }
      if (date_to) {
        conditions.push('DATE(o.booking_date) <= ?');
        params.push(date_to);
      }

      if (conditions.length > 0) {
        const whereClause = ' WHERE ' + conditions.join(' AND ');
        query += whereClause;
        countQuery += whereClause;
      }

      // 添加分页
      const offset = (page - 1) * pageSize;
      query += ` ORDER BY o.created_at DESC LIMIT ${pageSize} OFFSET ${offset}`;

      const [orders] = await db.execute(query, params);
      const [countResult] = await db.execute(countQuery, params);

      res.json({
        success: true,
        data: {
          orders: orders,
          total: countResult[0].total,
          page: parseInt(page),
          pageSize: parseInt(pageSize)
        }
      });
    } else {
      res.json({
        success: true,
        data: {
          orders: [],
          total: 0,
          page: parseInt(page),
          pageSize: parseInt(pageSize)
        }
      });
    }
  } catch (error) {
    console.error('获取订单列表错误:', error);
    res.status(500).json({
      success: false,
      message: '获取订单列表失败: ' + error.message
    });
  }
});



// 管理员台球桌API
app.get('/api/admin/tables', async (req, res) => {
  try {
    if (db) {
      const [rows] = await db.execute('SELECT * FROM tables ORDER BY created_at DESC');
      res.json({
        success: true,
        data: {
          tables: rows,
          total: rows.length
        }
      });
    } else {
      res.json({
        success: true,
        data: {
          tables: [],
          total: 0
        }
      });
    }
  } catch (error) {
    console.error('获取台球桌列表错误:', error);
    res.status(500).json({
      success: false,
      message: '获取台球桌列表失败: ' + error.message
    });
  }
});

// 创建订单API
app.post('/api/orders', async (req, res) => {
  try {
    const { table_id, table_name, table_type, booking_date, start_time, end_time, duration, total_cost, phone, remark } = req.body;
    const user_id = 1; // 实际应该从JWT token中解析

    // 生成订单号
    const order_number = 'ORD' + new Date().toISOString().slice(0, 10).replace(/-/g, '') + Math.random().toString().slice(2, 8);

    if (db) {
      // 检查时间段是否已被预订
      const [existing] = await db.execute(
        'SELECT id FROM orders WHERE table_id = ? AND booking_date = ? AND start_time = ? AND end_time = ? AND status IN ("pending", "confirmed", "using")',
        [table_id, booking_date, start_time, end_time]
      );

      if (existing.length > 0) {
        return res.status(400).json({
          success: false,
          message: '该时间段已被预订'
        });
      }

      // 创建订单
      const [result] = await db.execute(
        `INSERT INTO orders (order_number, user_id, table_id, table_name, table_type, booking_date, start_time, end_time, duration, total_cost, phone, remark, status, created_at)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'pending', NOW())`,
        [order_number, user_id, table_id, table_name, table_type, booking_date, start_time, end_time, duration, total_cost, phone, remark]
      );

      res.json({
        success: true,
        message: '订单创建成功',
        data: {
          id: result.insertId,
          order_number: `ORD${String(result.insertId).padStart(6, '0')}`
        }
      });
    } else {
      res.json({
        success: true,
        message: '订单创建成功（内存模式）',
        data: {
          id: Date.now(),
          order_number: `ORD${String(Date.now()).slice(-6)}`
        }
      });
    }
  } catch (error) {
    console.error('创建订单错误:', error);
    res.status(500).json({
      success: false,
      message: '创建订单失败: ' + error.message
    });
  }
});



// 台球桌管理API - 创建台球桌
app.post('/api/tables', async (req, res) => {
  try {
    const { name, type, hourly_rate, description } = req.body;

    if (db) {
      const [result] = await db.execute(
        'INSERT INTO tables (name, type, hourly_rate, description, status, created_at, updated_at) VALUES (?, ?, ?, ?, "available", NOW(), NOW())',
        [name, type, hourly_rate, description]
      );

      res.json({
        success: true,
        message: '台球桌创建成功',
        data: {
          id: result.insertId
        }
      });
    } else {
      res.json({
        success: true,
        message: '台球桌创建成功（内存模式）'
      });
    }
  } catch (error) {
    console.error('创建台球桌错误:', error);
    res.status(500).json({
      success: false,
      message: '创建台球桌失败: ' + error.message
    });
  }
});

// 更新订单状态API
app.put('/api/orders/:id/status', async (req, res) => {
  try {
    const { id } = req.params;
    const { status } = req.body;

    if (!status) {
      return res.status(400).json({
        success: false,
        message: '状态参数不能为空'
      });
    }

    // 验证状态值
    const validStatuses = ['pending', 'paid', 'using', 'completed', 'cancelled'];
    if (!validStatuses.includes(status)) {
      return res.status(400).json({
        success: false,
        message: '无效的订单状态'
      });
    }

    if (db) {
      const [result] = await db.execute(
        'UPDATE orders SET status = ?, updated_at = NOW() WHERE id = ?',
        [status, id]
      );

      if (result.affectedRows > 0) {
        res.json({
          success: true,
          message: '订单状态更新成功'
        });
      } else {
        res.status(404).json({
          success: false,
          message: '订单不存在'
        });
      }
    } else {
      res.json({
        success: true,
        message: '订单状态更新成功（内存模式）'
      });
    }
  } catch (error) {
    console.error('更新订单状态错误:', error);
    res.status(500).json({
      success: false,
      message: '更新订单状态失败: ' + error.message
    });
  }
});

// 管理员更新订单状态API
app.put('/api/admin/orders/:id/status', async (req, res) => {
  try {
    const { id } = req.params;
    const { status } = req.body;

    if (!status) {
      return res.status(400).json({
        success: false,
        message: '状态参数不能为空'
      });
    }

    // 验证状态值
    const validStatuses = ['pending', 'paid', 'using', 'completed', 'cancelled'];
    if (!validStatuses.includes(status)) {
      return res.status(400).json({
        success: false,
        message: '无效的订单状态'
      });
    }

    if (db) {
      const [result] = await db.execute(
        'UPDATE orders SET status = ?, updated_at = NOW() WHERE id = ?',
        [status, id]
      );

      if (result.affectedRows > 0) {
        res.json({
          success: true,
          message: '订单状态更新成功'
        });
      } else {
        res.status(404).json({
          success: false,
          message: '订单不存在'
        });
      }
    } else {
      res.json({
        success: true,
        message: '订单状态更新成功（内存模式）'
      });
    }
  } catch (error) {
    console.error('更新订单状态错误:', error);
    res.status(500).json({
      success: false,
      message: '更新订单状态失败: ' + error.message
    });
  }
});

// 台球桌管理API - 更新台球桌
app.put('/api/tables/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const { name, type, hourly_rate, description } = req.body;

    if (db) {
      await db.execute(
        'UPDATE tables SET name = ?, type = ?, hourly_rate = ?, description = ?, updated_at = NOW() WHERE id = ?',
        [name, type, hourly_rate, description, id]
      );

      res.json({
        success: true,
        message: '台球桌更新成功'
      });
    } else {
      res.json({
        success: true,
        message: '台球桌更新成功（内存模式）'
      });
    }
  } catch (error) {
    console.error('更新台球桌错误:', error);
    res.status(500).json({
      success: false,
      message: '更新台球桌失败: ' + error.message
    });
  }
});

// 台球桌管理API - 删除台球桌
app.delete('/api/tables/:id', async (req, res) => {
  try {
    const { id } = req.params;

    if (db) {
      await db.execute('DELETE FROM tables WHERE id = ?', [id]);

      res.json({
        success: true,
        message: '台球桌删除成功'
      });
    } else {
      res.json({
        success: true,
        message: '台球桌删除成功（内存模式）'
      });
    }
  } catch (error) {
    console.error('删除台球桌错误:', error);
    res.status(500).json({
      success: false,
      message: '删除台球桌失败: ' + error.message
    });
  }
});

// 台球桌状态更新API
app.put('/api/tables/:id/status', async (req, res) => {
  try {
    const { id } = req.params;
    const { status } = req.body;

    if (db) {
      await db.execute(
        'UPDATE tables SET status = ?, updated_at = NOW() WHERE id = ?',
        [status, id]
      );

      res.json({
        success: true,
        message: '台球桌状态更新成功'
      });
    } else {
      res.json({
        success: true,
        message: '台球桌状态更新成功（内存模式）'
      });
    }
  } catch (error) {
    console.error('更新台球桌状态错误:', error);
    res.status(500).json({
      success: false,
      message: '更新台球桌状态失败: ' + error.message
    });
  }
});

// 管理员更新用户余额API
app.put('/api/admin/users/:id/balance', async (req, res) => {
  try {
    const { id } = req.params;
    const { balance, operation = 'set' } = req.body;

    if (!balance && balance !== 0) {
      return res.status(400).json({
        success: false,
        message: '余额参数不能为空'
      });
    }

    if (db) {
      // 根据操作类型更新余额
      let updateQuery;
      let updateParams;

      switch (operation) {
        case 'add':
          updateQuery = 'UPDATE users SET balance = balance + ?, updated_at = NOW() WHERE id = ?';
          updateParams = [balance, id];
          break;
        case 'subtract':
          updateQuery = 'UPDATE users SET balance = GREATEST(0, balance - ?), updated_at = NOW() WHERE id = ?';
          updateParams = [balance, id];
          break;
        case 'set':
        default:
          updateQuery = 'UPDATE users SET balance = ?, updated_at = NOW() WHERE id = ?';
          updateParams = [balance, id];
          break;
      }

      const [result] = await db.execute(updateQuery, updateParams);

      if (result.affectedRows > 0) {
        // 获取更新后的用户信息
        const [rows] = await db.execute(
          'SELECT id, username, phone, email, real_name, role, status, balance, play_time, avatar, created_at, updated_at FROM users WHERE id = ?',
          [id]
        );

        res.json({
          success: true,
          message: '用户余额更新成功',
          data: rows[0]
        });
      } else {
        res.status(404).json({
          success: false,
          message: '用户不存在'
        });
      }
    } else {
      // 内存数据库模式
      const user = memoryDB.users.find(u => u.id == id);
      if (user) {
        const currentBalance = parseFloat(user.balance) || 0;
        const amount = parseFloat(balance) || 0;

        switch (operation) {
          case 'add':
            user.balance = (currentBalance + amount).toFixed(2);
            break;
          case 'subtract':
            user.balance = Math.max(0, currentBalance - amount).toFixed(2);
            break;
          case 'set':
          default:
            user.balance = amount.toFixed(2);
            break;
        }

        user.updated_at = new Date();

        res.json({
          success: true,
          message: '用户余额更新成功',
          data: user
        });
      } else {
        res.status(404).json({
          success: false,
          message: '用户不存在'
        });
      }
    }
  } catch (error) {
    console.error('更新用户余额错误:', error);
    res.status(500).json({
      success: false,
      message: '更新用户余额失败: ' + error.message
    });
  }
});

// 管理员更新用户信息API
app.put('/api/admin/users/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const { username, email, real_name, role, status } = req.body;

    if (db) {
      const [result] = await db.execute(
        'UPDATE users SET username = ?, email = ?, real_name = ?, role = ?, status = ?, updated_at = NOW() WHERE id = ?',
        [username, email, real_name, role, status, id]
      );

      if (result.affectedRows > 0) {
        const [rows] = await db.execute(
          'SELECT id, username, phone, email, real_name, role, status, balance, play_time, avatar, created_at, updated_at FROM users WHERE id = ?',
          [id]
        );

        res.json({
          success: true,
          message: '用户信息更新成功',
          data: rows[0]
        });
      } else {
        res.status(404).json({
          success: false,
          message: '用户不存在'
        });
      }
    } else {
      // 内存数据库模式
      const user = memoryDB.users.find(u => u.id == id);
      if (user) {
        user.username = username;
        user.email = email;
        user.real_name = real_name;
        user.role = role;
        user.status = status;
        user.updated_at = new Date();

        res.json({
          success: true,
          message: '用户信息更新成功',
          data: user
        });
      } else {
        res.status(404).json({
          success: false,
          message: '用户不存在'
        });
      }
    }
  } catch (error) {
    console.error('更新用户信息错误:', error);
    res.status(500).json({
      success: false,
      message: '更新用户信息失败: ' + error.message
    });
  }
});

// 管理员切换用户状态API
app.put('/api/admin/users/:id/status', async (req, res) => {
  try {
    const { id } = req.params;
    const { status } = req.body;

    if (!['active', 'inactive', 'banned'].includes(status)) {
      return res.status(400).json({
        success: false,
        message: '无效的用户状态'
      });
    }

    if (db) {
      const [result] = await db.execute(
        'UPDATE users SET status = ?, updated_at = NOW() WHERE id = ?',
        [status, id]
      );

      if (result.affectedRows > 0) {
        res.json({
          success: true,
          message: '用户状态更新成功'
        });
      } else {
        res.status(404).json({
          success: false,
          message: '用户不存在'
        });
      }
    } else {
      // 内存数据库模式
      const user = memoryDB.users.find(u => u.id == id);
      if (user) {
        user.status = status;
        user.updated_at = new Date();

        res.json({
          success: true,
          message: '用户状态更新成功'
        });
      } else {
        res.status(404).json({
          success: false,
          message: '用户不存在'
        });
      }
    }
  } catch (error) {
    console.error('更新用户状态错误:', error);
    res.status(500).json({
      success: false,
      message: '更新用户状态失败: ' + error.message
    });
  }
});

// 管理员删除用户API
app.delete('/api/admin/users/:id', async (req, res) => {
  try {
    const { id } = req.params;

    if (db) {
      const [result] = await db.execute('DELETE FROM users WHERE id = ?', [id]);

      if (result.affectedRows > 0) {
        res.json({
          success: true,
          message: '用户删除成功'
        });
      } else {
        res.status(404).json({
          success: false,
          message: '用户不存在'
        });
      }
    } else {
      // 内存数据库模式
      const userIndex = memoryDB.users.findIndex(u => u.id == id);
      if (userIndex !== -1) {
        memoryDB.users.splice(userIndex, 1);

        res.json({
          success: true,
          message: '用户删除成功'
        });
      } else {
        res.status(404).json({
          success: false,
          message: '用户不存在'
        });
      }
    }
  } catch (error) {
    console.error('删除用户错误:', error);
    res.status(500).json({
      success: false,
      message: '删除用户失败: ' + error.message
    });
  }
});

// 获取当前用户信息API
app.get('/api/auth/me', async (req, res) => {
  try {
    // 这里应该从token中获取用户ID，暂时使用固定用户
    const userId = 1; // 实际应该从JWT token中解析

    if (db) {
      const [rows] = await db.execute(
        'SELECT id, username, phone, email, real_name, role, status, balance, play_time, avatar, created_at FROM users WHERE id = ?',
        [userId]
      );

      if (rows.length > 0) {
        const user = rows[0];
        res.json({
          success: true,
          data: {
            user: {
              id: user.id,
              username: user.username,
              realName: user.real_name,
              email: user.email,
              phone: user.phone,
              role: user.role,
              avatar: user.avatar,
              status: user.status,
              balance: user.balance,
              playTime: user.play_time,
              createdAt: user.created_at
            }
          }
        });
      } else {
        res.status(404).json({
          success: false,
          message: '用户不存在'
        });
      }
    } else {
      const user = memoryDB.users.find(u => u.id === userId);
      if (user) {
        res.json({
          success: true,
          data: {
            user: {
              id: user.id,
              username: user.username,
              realName: user.real_name || '',
              email: user.email || '',
              phone: user.phone,
              role: user.role,
              avatar: user.avatar || '',
              status: user.status,
              balance: user.balance,
              playTime: user.play_time,
              createdAt: user.created_at
            }
          }
        });
      } else {
        res.status(404).json({
          success: false,
          message: '用户不存在'
        });
      }
    }
  } catch (error) {
    console.error('获取用户信息错误:', error);
    res.status(500).json({
      success: false,
      message: '获取用户信息失败: ' + error.message
    });
  }
});

// 用户注册API
app.post('/api/auth/register', async (req, res) => {
  try {
    const { username, phone, password, email, real_name } = req.body;

    if (!username || !phone || !password) {
      return res.status(400).json({
        success: false,
        message: '用户名、手机号和密码不能为空'
      });
    }

    if (db) {
      // 检查用户名和手机号是否已存在
      const [existing] = await db.execute(
        'SELECT id FROM users WHERE username = ? OR phone = ?',
        [username, phone]
      );

      if (existing.length > 0) {
        return res.status(400).json({
          success: false,
          message: '用户名或手机号已存在'
        });
      }

      // 创建新用户
      const [result] = await db.execute(
        'INSERT INTO users (username, phone, password, email, real_name, role, status, balance, play_time, created_at) VALUES (?, ?, ?, ?, ?, "user", "active", 0.00, 0, NOW())',
        [username, phone, password, email || '', real_name || '']
      );

      res.json({
        success: true,
        message: '注册成功',
        data: {
          token: 'mysql-token-' + Date.now(),
          user: {
            id: result.insertId,
            username,
            phone,
            role: 'user'
          }
        }
      });
    } else {
      // 内存数据库模式
      const newUser = {
        id: Date.now(),
        username,
        phone,
        password,
        email: email || '',
        real_name: real_name || '',
        role: 'user',
        status: 'active',
        balance: '0.00',
        play_time: 0,
        created_at: new Date().toISOString()
      };

      memoryDB.users.push(newUser);

      res.json({
        success: true,
        message: '注册成功',
        data: {
          token: 'memory-token-' + Date.now(),
          user: {
            id: newUser.id,
            username: newUser.username,
            phone: newUser.phone,
            role: newUser.role
          }
        }
      });
    }
  } catch (error) {
    console.error('用户注册错误:', error);
    res.status(500).json({
      success: false,
      message: '注册失败: ' + error.message
    });
  }
});

// 用户个人信息API
app.get('/api/user/profile', async (req, res) => {
  try {
    // 这里应该从token中获取用户ID，暂时使用固定用户
    const userId = 1; // 实际应该从JWT token中解析

    if (db) {
      const [rows] = await db.execute(
        'SELECT id, username, phone, email, real_name, role, status, balance, play_time, avatar, created_at FROM users WHERE id = ?',
        [userId]
      );

      if (rows.length > 0) {
        res.json({
          success: true,
          data: rows[0]
        });
      } else {
        res.status(404).json({
          success: false,
          message: '用户不存在'
        });
      }
    } else {
      const user = memoryDB.users.find(u => u.id === userId);
      if (user) {
        res.json({
          success: true,
          data: {
            id: user.id,
            username: user.username,
            phone: user.phone,
            email: user.email || '',
            real_name: user.real_name || '',
            role: user.role,
            status: user.status,
            balance: user.balance,
            play_time: user.play_time,
            avatar: user.avatar || '',
            created_at: user.created_at
          }
        });
      } else {
        res.status(404).json({
          success: false,
          message: '用户不存在'
        });
      }
    }
  } catch (error) {
    console.error('获取用户信息错误:', error);
    res.status(500).json({
      success: false,
      message: '获取用户信息失败: ' + error.message
    });
  }
});

// 获取用户余额API
app.get('/api/user/balance', async (req, res) => {
  try {
    // 这里应该从token中获取用户ID，暂时使用固定用户
    const userId = 1; // 实际应该从JWT token中解析

    if (db) {
      const [rows] = await db.execute(
        'SELECT balance FROM users WHERE id = ?',
        [userId]
      );

      if (rows.length > 0) {
        res.json({
          success: true,
          data: {
            balance: rows[0].balance
          }
        });
      } else {
        res.status(404).json({
          success: false,
          message: '用户不存在'
        });
      }
    } else {
      const user = memoryDB.users.find(u => u.id === userId);
      if (user) {
        res.json({
          success: true,
          data: {
            balance: user.balance
          }
        });
      } else {
        res.status(404).json({
          success: false,
          message: '用户不存在'
        });
      }
    }
  } catch (error) {
    console.error('获取用户余额错误:', error);
    res.status(500).json({
      success: false,
      message: '获取用户余额失败: ' + error.message
    });
  }
});

// 用户充值API
app.post('/api/user/recharge', async (req, res) => {
  try {
    const { amount, paymentMethod, bonusAmount = 0 } = req.body;
    const userId = 1; // 实际应该从JWT token中解析

    if (!amount || amount <= 0) {
      return res.status(400).json({
        success: false,
        message: '充值金额无效'
      });
    }

    const totalAmount = parseFloat(amount) + parseFloat(bonusAmount);

    if (db) {
      try {
        // 直接更新用户余额，不使用事务（简化处理）
        await db.execute(
          'UPDATE users SET balance = balance + ? WHERE id = ?',
          [totalAmount, userId]
        );

        res.json({
          success: true,
          message: '充值成功',
          data: {
            amount: amount,
            bonusAmount: bonusAmount,
            totalAmount: totalAmount
          }
        });
      } catch (error) {
        throw error;
      }
    } else {
      // 内存数据库模式
      const user = memoryDB.users.find(u => u.id === userId);
      if (user) {
        user.balance = (parseFloat(user.balance) + totalAmount).toFixed(2);
        res.json({
          success: true,
          message: '充值成功',
          data: {
            amount: amount,
            bonusAmount: bonusAmount,
            totalAmount: totalAmount
          }
        });
      } else {
        res.status(404).json({
          success: false,
          message: '用户不存在'
        });
      }
    }
  } catch (error) {
    console.error('用户充值错误:', error);
    res.status(500).json({
      success: false,
      message: '充值失败: ' + error.message
    });
  }
});

// 获取充值记录API
app.get('/api/user/recharge/history', async (req, res) => {
  try {
    const userId = 1; // 实际应该从JWT token中解析

    // 暂时返回模拟数据，实际应该从数据库查询
    res.json({
      success: true,
      data: {
        records: [
          {
            id: 'PAY001',
            amount: 100,
            bonus: 10,
            paymentMethod: '支付宝',
            status: 'success',
            createTime: '2025-06-18 10:30:00'
          },
          {
            id: 'PAY002',
            amount: 50,
            bonus: 0,
            paymentMethod: '微信支付',
            status: 'success',
            createTime: '2025-06-17 15:20:00'
          }
        ]
      }
    });
  } catch (error) {
    console.error('获取充值记录错误:', error);
    res.status(500).json({
      success: false,
      message: '获取充值记录失败: ' + error.message
    });
  }
});

// 生成支付二维码API
app.post('/api/user/payment/qrcode', async (req, res) => {
  try {
    const { orderId, amount } = req.body;
    const userId = 1; // 实际应该从JWT token中解析

    if (!orderId || !amount) {
      return res.status(400).json({
        success: false,
        message: '缺少必要参数'
      });
    }

    // 生成虚拟二维码数据
    const qrData = `PAYMENT:${orderId}:${amount}:${userId}:${Date.now()}`;

    res.json({
      success: true,
      data: {
        qrData: qrData,
        orderId: orderId,
        amount: amount,
        expireTime: new Date(Date.now() + 5 * 60 * 1000).toISOString() // 5分钟后过期
      }
    });
  } catch (error) {
    console.error('生成支付二维码错误:', error);
    res.status(500).json({
      success: false,
      message: '生成支付二维码失败: ' + error.message
    });
  }
});

// 处理支付API
app.post('/api/user/payment/pay', async (req, res) => {
  try {
    const { orderId, amount, qrData } = req.body;
    const userId = 1; // 实际应该从JWT token中解析

    if (!orderId || !amount) {
      return res.status(400).json({
        success: false,
        message: '缺少必要参数'
      });
    }

    if (db) {
      // 检查用户余额
      const [userRows] = await db.execute(
        'SELECT balance FROM users WHERE id = ?',
        [userId]
      );

      if (userRows.length === 0) {
        return res.status(404).json({
          success: false,
          message: '用户不存在'
        });
      }

      const currentBalance = parseFloat(userRows[0].balance);
      const paymentAmount = parseFloat(amount);

      if (currentBalance < paymentAmount) {
        return res.status(400).json({
          success: false,
          message: '余额不足'
        });
      }

      // 扣除余额
      const newBalance = currentBalance - paymentAmount;
      await db.execute(
        'UPDATE users SET balance = ? WHERE id = ?',
        [newBalance, userId]
      );

      res.json({
        success: true,
        message: '支付成功',
        data: {
          orderId: orderId,
          amount: paymentAmount,
          remainingBalance: newBalance,
          paymentTime: new Date().toISOString()
        }
      });
    } else {
      // 内存数据库模式
      const user = memoryDB.users.find(u => u.id === userId);
      if (!user) {
        return res.status(404).json({
          success: false,
          message: '用户不存在'
        });
      }

      const currentBalance = parseFloat(user.balance);
      const paymentAmount = parseFloat(amount);

      if (currentBalance < paymentAmount) {
        return res.status(400).json({
          success: false,
          message: '余额不足'
        });
      }

      user.balance = (currentBalance - paymentAmount).toFixed(2);

      res.json({
        success: true,
        message: '支付成功',
        data: {
          orderId: orderId,
          amount: paymentAmount,
          remainingBalance: parseFloat(user.balance),
          paymentTime: new Date().toISOString()
        }
      });
    }
  } catch (error) {
    console.error('处理支付错误:', error);
    res.status(500).json({
      success: false,
      message: '支付失败: ' + error.message
    });
  }
});

// 更新用户信息API
app.put('/api/user/profile', async (req, res) => {
  try {
    const { email, phone, real_name } = req.body;
    const userId = 1; // 实际应该从JWT token中解析

    if (db) {
      await db.execute(
        'UPDATE users SET email = ?, phone = ?, real_name = ?, updated_at = NOW() WHERE id = ?',
        [email, phone, real_name, userId]
      );

      // 返回更新后的用户信息
      const [rows] = await db.execute(
        'SELECT id, username, phone, email, real_name, role, status, balance, play_time, avatar, created_at FROM users WHERE id = ?',
        [userId]
      );

      if (rows.length > 0) {
        res.json({
          success: true,
          message: '用户信息更新成功',
          data: rows[0]
        });
      } else {
        res.status(404).json({
          success: false,
          message: '用户不存在'
        });
      }
    } else {
      const user = memoryDB.users.find(u => u.id === userId);
      if (user) {
        user.email = email;
        user.phone = phone;
        user.real_name = real_name;
        user.updated_at = new Date();

        res.json({
          success: true,
          message: '用户信息更新成功',
          data: {
            id: user.id,
            username: user.username,
            phone: user.phone,
            email: user.email,
            real_name: user.real_name,
            role: user.role,
            status: user.status,
            balance: user.balance,
            play_time: user.play_time,
            avatar: user.avatar || '',
            created_at: user.created_at
          }
        });
      } else {
        res.status(404).json({
          success: false,
          message: '用户不存在'
        });
      }
    }
  } catch (error) {
    console.error('更新用户信息错误:', error);
    res.status(500).json({
      success: false,
      message: '更新用户信息失败: ' + error.message
    });
  }
});

// 修改密码API
app.put('/api/user/password', async (req, res) => {
  try {
    const { currentPassword, newPassword } = req.body;
    const userId = 1; // 实际应该从JWT token中解析

    // 这里应该验证当前密码，暂时简化处理
    if (db) {
      // 实际应该先验证当前密码，然后加密新密码
      await db.execute(
        'UPDATE users SET password = ?, updated_at = NOW() WHERE id = ?',
        [newPassword, userId] // 实际应该加密密码
      );

      res.json({
        success: true,
        message: '密码修改成功'
      });
    } else {
      const user = memoryDB.users.find(u => u.id === userId);
      if (user) {
        // 简化验证：检查当前密码
        if (user.password !== currentPassword) {
          return res.status(400).json({
            success: false,
            message: '当前密码错误'
          });
        }

        user.password = newPassword;
        user.updated_at = new Date();

        res.json({
          success: true,
          message: '密码修改成功'
        });
      } else {
        res.status(404).json({
          success: false,
          message: '用户不存在'
        });
      }
    }
  } catch (error) {
    console.error('修改密码错误:', error);
    res.status(500).json({
      success: false,
      message: '修改密码失败: ' + error.message
    });
  }
});

// 注意：头像上传API已移至 routes/users.js 中，使用 multer 处理文件上传

// 获取用户设置API
app.get('/api/user/settings', async (req, res) => {
  try {
    // 返回默认设置
    res.json({
      success: true,
      data: {
        showOnlineStatus: true,
        allowFriendInvites: true,
        showGameStats: true,
        emailNotifications: true,
        smsNotifications: false,
        pushNotifications: true,
        twoFactorEnabled: false,
        loginAlerts: true,
        autoLogoutTime: 60
      }
    });
  } catch (error) {
    console.error('获取用户设置错误:', error);
    res.status(500).json({
      success: false,
      message: '获取用户设置失败: ' + error.message
    });
  }
});

// 更新用户设置API
app.put('/api/user/settings', async (req, res) => {
  try {
    const settings = req.body;
    const userId = 1; // 实际应该从JWT token中解析

    // 这里应该将设置保存到数据库，暂时简化处理
    res.json({
      success: true,
      message: '设置保存成功',
      data: settings
    });
  } catch (error) {
    console.error('更新用户设置错误:', error);
    res.status(500).json({
      success: false,
      message: '更新用户设置失败: ' + error.message
    });
  }
});

// 错误处理
app.use((err, req, res, next) => {
  console.error('服务器错误:', err);
  res.status(500).json({
    success: false,
    message: '服务器内部错误: ' + err.message
  });
});



// 库存管理API
app.get('/api/inventory', async (req, res) => {
  try {
    // 返回模拟库存数据
    const inventory = [
      {
        id: 1,
        name: '台球杆',
        sku: 'TQG001',
        category: '设备',
        current_stock: 50,
        min_stock: 10,
        max_stock: 100,
        unit: '支',
        unit_price: 150.00,
        supplier: '台球设备公司',
        status: 'active',
        created_at: new Date('2025-01-01'),
        updated_at: new Date()
      },
      {
        id: 2,
        name: '台球',
        sku: 'TQ001',
        category: '消耗品',
        current_stock: 200,
        min_stock: 50,
        max_stock: 500,
        unit: '个',
        unit_price: 25.00,
        supplier: '台球设备公司',
        status: 'active',
        created_at: new Date('2025-01-01'),
        updated_at: new Date()
      },
      {
        id: 3,
        name: '台球桌布',
        sku: 'TQB001',
        category: '设备',
        current_stock: 8,
        min_stock: 10,
        max_stock: 50,
        unit: '张',
        unit_price: 300.00,
        supplier: '台球设备公司',
        status: 'active',
        created_at: new Date('2025-01-01'),
        updated_at: new Date()
      }
    ];

    res.json({
      success: true,
      data: {
        items: inventory,
        total: inventory.length
      }
    });
  } catch (error) {
    console.error('获取库存列表错误:', error);
    res.status(500).json({
      success: false,
      message: '获取库存列表失败: ' + error.message
    });
  }
});

app.get('/api/inventory/stats', async (req, res) => {
  try {
    res.json({
      success: true,
      data: {
        totalItems: 3,
        lowStockItems: 1,
        outOfStockItems: 0,
        totalValue: 12500.00
      }
    });
  } catch (error) {
    console.error('获取库存统计错误:', error);
    res.status(500).json({
      success: false,
      message: '获取库存统计失败: ' + error.message
    });
  }
});

// 财务报表API
app.get('/api/finance/overview', async (req, res) => {
  try {
    if (db) {
      const [revenueResult] = await db.execute('SELECT COALESCE(SUM(total_cost), 0) as total FROM orders WHERE status IN ("paid", "completed")');
      const [todayRevenueResult] = await db.execute('SELECT COALESCE(SUM(total_cost), 0) as today FROM orders WHERE status IN ("paid", "completed") AND DATE(created_at) = CURDATE()');

      res.json({
        success: true,
        data: {
          totalRevenue: revenueResult[0].total,
          todayRevenue: todayRevenueResult[0].today,
          monthlyRevenue: revenueResult[0].total * 0.3,
          profit: revenueResult[0].total * 0.6,
          expenses: revenueResult[0].total * 0.4
        }
      });
    } else {
      res.json({
        success: true,
        data: {
          totalRevenue: 0,
          todayRevenue: 0,
          monthlyRevenue: 0,
          profit: 0,
          expenses: 0
        }
      });
    }
  } catch (error) {
    console.error('获取财务概览错误:', error);
    res.status(500).json({
      success: false,
      message: '获取财务概览失败: ' + error.message
    });
  }
});

app.get('/api/finance/revenue-trend', async (req, res) => {
  try {
    // 返回模拟收入趋势数据
    const trendData = [];
    for (let i = 29; i >= 0; i--) {
      const date = new Date();
      date.setDate(date.getDate() - i);
      trendData.push({
        date: date.toISOString().split('T')[0],
        revenue: Math.floor(Math.random() * 1000) + 500
      });
    }

    res.json({
      success: true,
      data: trendData
    });
  } catch (error) {
    console.error('获取收入趋势错误:', error);
    res.status(500).json({
      success: false,
      message: '获取收入趋势失败: ' + error.message
    });
  }
});

// 台球桌分析API
app.get('/api/finance/table-analysis', async (req, res) => {
  try {
    if (db) {
      const [analysisResult] = await db.execute(`
        SELECT
          t.id,
          t.name as table_name,
          t.type,
          t.hourly_rate,
          COUNT(o.id) as order_count,
          COALESCE(SUM(o.duration), 0) as total_hours,
          COALESCE(SUM(o.total_cost), 0) as total_revenue,
          COALESCE(AVG(o.total_cost), 0) as avg_revenue_per_order,
          CASE
            WHEN COUNT(o.id) > 0 THEN (SUM(o.duration) / 30) * 100
            ELSE 0
          END as utilization_rate
        FROM tables t
        LEFT JOIN orders o ON t.id = o.table_id
          AND o.status IN ('completed', 'paid')
          AND o.created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
        GROUP BY t.id, t.name, t.type, t.hourly_rate
        ORDER BY total_revenue DESC
      `);

      res.json({
        success: true,
        data: {
          analysis: analysisResult
        }
      });
    } else {
      // 内存数据库模式，返回模拟数据
      const mockAnalysis = [
        {
          id: 1,
          table_name: 'A001',
          type: '美式台球',
          hourly_rate: 25,
          order_count: 15,
          total_hours: 30,
          total_revenue: 750,
          avg_revenue_per_order: 50,
          utilization_rate: 85
        },
        {
          id: 2,
          table_name: 'A002',
          type: '美式台球',
          hourly_rate: 25,
          order_count: 12,
          total_hours: 24,
          total_revenue: 600,
          avg_revenue_per_order: 50,
          utilization_rate: 70
        },
        {
          id: 3,
          table_name: 'B001',
          type: '中式台球',
          hourly_rate: 20,
          order_count: 8,
          total_hours: 16,
          total_revenue: 320,
          avg_revenue_per_order: 40,
          utilization_rate: 55
        }
      ];

      res.json({
        success: true,
        data: {
          analysis: mockAnalysis
        }
      });
    }
  } catch (error) {
    console.error('获取台球桌分析错误:', error);
    res.status(500).json({
      success: false,
      message: '获取台球桌分析失败: ' + error.message
    });
  }
});

// 统计数据API
app.get('/api/stats/overview', async (req, res) => {
  try {
    if (db) {
      // 获取用户统计
      const [userStats] = await db.execute('SELECT COUNT(*) as total, SUM(CASE WHEN status = "active" THEN 1 ELSE 0 END) as active FROM users');

      // 获取订单统计
      const [orderStats] = await db.execute('SELECT COUNT(*) as total, SUM(total_cost) as revenue FROM orders');

      // 获取今日订单统计
      const [todayStats] = await db.execute('SELECT COUNT(*) as today_orders, SUM(total_cost) as today_revenue FROM orders WHERE DATE(created_at) = CURDATE()');

      // 获取台球桌统计
      const [tableStats] = await db.execute('SELECT COUNT(*) as total, SUM(CASE WHEN status = "occupied" THEN 1 ELSE 0 END) as occupied FROM tables');

      res.json({
        success: true,
        data: {
          totalUsers: userStats[0].total,
          activeUsers: userStats[0].active,
          totalOrders: orderStats[0].total,
          ordersToday: todayStats[0].today_orders,
          totalRevenue: orderStats[0].revenue || 0,
          revenueToday: todayStats[0].today_revenue || 0,
          totalTables: tableStats[0].total,
          activeTables: tableStats[0].occupied
        }
      });
    } else {
      res.json({
        success: true,
        data: {
          totalUsers: memoryDB.users.length,
          activeUsers: memoryDB.users.filter(u => u.status === 'active').length,
          totalOrders: 0,
          ordersToday: 0,
          totalRevenue: 0,
          revenueToday: 0,
          totalTables: 0,
          activeTables: 0
        }
      });
    }
  } catch (error) {
    console.error('获取统计数据错误:', error);
    res.status(500).json({
      success: false,
      message: '获取统计数据失败: ' + error.message
    });
  }
});

// 用户统计API
app.get('/api/admin/users/stats', async (req, res) => {
  try {
    if (db) {
      // 获取用户总数和活跃用户数
      const [userCounts] = await db.execute(`
        SELECT
          COUNT(*) as totalUsers,
          SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) as activeUsers,
          SUM(CASE WHEN role = 'admin' THEN 1 ELSE 0 END) as adminUsers,
          SUM(CASE WHEN DATE(created_at) = CURDATE() THEN 1 ELSE 0 END) as newUsersToday
        FROM users
      `);

      // 获取用户注册趋势（最近30天）
      const [registrationTrend] = await db.execute(`
        SELECT
          DATE(created_at) as date,
          COUNT(*) as count
        FROM users
        WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
        GROUP BY DATE(created_at)
        ORDER BY date
      `);

      // 获取用户余额分布
      const [balanceDistribution] = await db.execute(`
        SELECT
          CASE
            WHEN balance = 0 THEN '0元'
            WHEN balance > 0 AND balance <= 100 THEN '1-100元'
            WHEN balance > 100 AND balance <= 500 THEN '101-500元'
            WHEN balance > 500 THEN '500元以上'
          END as range,
          COUNT(*) as count
        FROM users
        GROUP BY range
      `);

      res.json({
        success: true,
        data: {
          ...userCounts[0],
          registrationTrend,
          balanceDistribution
        }
      });
    } else {
      res.json({
        success: true,
        data: {
          totalUsers: memoryDB.users.length,
          activeUsers: memoryDB.users.filter(u => u.status === 'active').length,
          adminUsers: memoryDB.users.filter(u => u.role === 'admin').length,
          newUsersToday: 0,
          registrationTrend: [],
          balanceDistribution: []
        }
      });
    }
  } catch (error) {
    console.error('获取用户统计错误:', error);
    res.status(500).json({
      success: false,
      message: '获取用户统计失败: ' + error.message
    });
  }
});

// 订单统计API
app.get('/api/admin/orders/stats', async (req, res) => {
  try {
    if (db) {
      // 获取订单总数和收入统计
      const [orderCounts] = await db.execute(`
        SELECT
          COUNT(*) as total,
          SUM(total_cost) as totalRevenue,
          SUM(CASE WHEN DATE(created_at) = CURDATE() THEN 1 ELSE 0 END) as today,
          SUM(CASE WHEN DATE(created_at) = CURDATE() THEN total_cost ELSE 0 END) as todayRevenue
        FROM orders
      `);

      // 获取订单状态分布
      const [statusStats] = await db.execute(`
        SELECT
          status,
          COUNT(*) as count
        FROM orders
        GROUP BY status
      `);

      // 获取最近30天订单趋势
      const [orderTrend] = await db.execute(`
        SELECT
          DATE(created_at) as date,
          COUNT(*) as count,
          SUM(total_cost) as revenue
        FROM orders
        WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
        GROUP BY DATE(created_at)
        ORDER BY date
      `);

      // 转换状态统计为对象格式
      const statusStatsObj = {};
      statusStats.forEach(stat => {
        statusStatsObj[stat.status] = stat.count;
      });

      res.json({
        success: true,
        data: {
          ...orderCounts[0],
          statusStats: statusStatsObj,
          dailyTrend: orderTrend,  // 前端期望的字段名
          statusDistribution: statusStats  // 前端期望的字段名
        }
      });
    } else {
      // 内存数据库模式，生成模拟数据
      const mockDailyTrend = [];
      const mockStatusDistribution = [
        { status: 'pending', count: 3 },
        { status: 'completed', count: 8 },
        { status: 'using', count: 2 },
        { status: 'cancelled', count: 1 }
      ];

      // 生成最近7天的模拟趋势数据
      for (let i = 6; i >= 0; i--) {
        const date = new Date();
        date.setDate(date.getDate() - i);
        mockDailyTrend.push({
          date: date.toISOString().split('T')[0],
          count: Math.floor(Math.random() * 5) + 1,
          revenue: Math.floor(Math.random() * 500) + 100
        });
      }

      res.json({
        success: true,
        data: {
          total: 14,
          totalRevenue: 2100,
          today: 3,
          todayRevenue: 150,
          statusStats: {
            pending: 3,
            completed: 8,
            using: 2,
            cancelled: 1
          },
          dailyTrend: mockDailyTrend,
          statusDistribution: mockStatusDistribution
        }
      });
    }
  } catch (error) {
    console.error('获取订单统计错误:', error);
    res.status(500).json({
      success: false,
      message: '获取订单统计失败: ' + error.message
    });
  }
});

// 获取已预订时间段API
app.get('/api/orders/booked-slots', async (req, res) => {
  try {
    const { table_id, date } = req.query;

    if (!table_id || !date) {
      return res.status(400).json({
        success: false,
        message: '台球桌ID和日期参数必填'
      });
    }

    if (db) {
      const [rows] = await db.execute(
        `SELECT start_time, end_time
         FROM orders
         WHERE table_id = ?
           AND booking_date = ?
           AND status IN ('pending', 'confirmed')
         ORDER BY start_time`,
        [table_id, date]
      );

      res.json({
        success: true,
        data: rows
      });
    } else {
      // 内存数据库模式
      const bookedSlots = memoryDB.orders
        .filter(order =>
          order.table_id == table_id &&
          order.booking_date === date &&
          ['pending', 'confirmed'].includes(order.status)
        )
        .map(order => ({
          start_time: order.start_time,
          end_time: order.end_time
        }))
        .sort((a, b) => a.start_time.localeCompare(b.start_time));

      res.json({
        success: true,
        data: bookedSlots
      });
    }
  } catch (error) {
    console.error('获取已预订时间段错误:', error);
    res.status(500).json({
      success: false,
      message: '获取已预订时间段失败: ' + error.message
    });
  }
});

// 获取用户订单列表API
app.get('/api/orders/user', async (req, res) => {
  try {
    const userId = 1; // 实际应该从JWT token中解析
    const { page = 1, pageSize = 10, status } = req.query;
    const offset = (page - 1) * pageSize;

    if (db) {
      let whereClause = 'WHERE o.user_id = ?';
      let params = [userId];

      if (status && status !== 'all') {
        whereClause += ' AND o.status = ?';
        params.push(status);
      }

      const [rows] = await db.execute(
        `SELECT o.*, t.name as table_name, t.type as table_type
         FROM orders o
         LEFT JOIN tables t ON o.table_id = t.id
         ${whereClause}
         ORDER BY o.created_at DESC
         LIMIT ? OFFSET ?`,
        [...params, parseInt(pageSize), offset]
      );

      const [countResult] = await db.execute(
        `SELECT COUNT(*) as total FROM orders o ${whereClause}`,
        params
      );

      res.json({
        success: true,
        data: {
          items: rows,
          total: countResult[0].total,
          page: parseInt(page),
          pageSize: parseInt(pageSize)
        }
      });
    } else {
      // 内存数据库模式
      let userOrders = memoryDB.orders.filter(order => order.user_id === userId);

      if (status && status !== 'all') {
        userOrders = userOrders.filter(order => order.status === status);
      }

      // 添加台球桌信息
      userOrders = userOrders.map(order => {
        const table = memoryDB.tables.find(t => t.id === order.table_id);
        return {
          ...order,
          table_name: table ? table.name : '未知',
          table_type: table ? table.type : '未知'
        };
      });

      // 排序和分页
      userOrders.sort((a, b) => new Date(b.created_at) - new Date(a.created_at));
      const total = userOrders.length;
      const items = userOrders.slice(offset, offset + parseInt(pageSize));

      res.json({
        success: true,
        data: {
          items,
          total,
          page: parseInt(page),
          pageSize: parseInt(pageSize)
        }
      });
    }
  } catch (error) {
    console.error('获取用户订单列表错误:', error);
    res.status(500).json({
      success: false,
      message: '获取用户订单列表失败: ' + error.message
    });
  }
});

// 获取充值记录API
app.get('/api/user/recharge/history', async (req, res) => {
  try {
    const userId = 1; // 实际应该从JWT token中解析
    const { page = 1, pageSize = 10 } = req.query;
    const offset = (page - 1) * pageSize;

    if (db) {
      const [rows] = await db.execute(
        `SELECT * FROM recharge_records
         WHERE user_id = ?
         ORDER BY created_at DESC
         LIMIT ? OFFSET ?`,
        [userId, parseInt(pageSize), offset]
      );

      const [countResult] = await db.execute(
        'SELECT COUNT(*) as total FROM recharge_records WHERE user_id = ?',
        [userId]
      );

      res.json({
        success: true,
        data: {
          items: rows,
          total: countResult[0].total,
          page: parseInt(page),
          pageSize: parseInt(pageSize)
        }
      });
    } else {
      // 内存数据库模式
      const userRecharges = memoryDB.rechargeRecords
        .filter(record => record.user_id === userId)
        .sort((a, b) => new Date(b.created_at) - new Date(a.created_at));

      const total = userRecharges.length;
      const items = userRecharges.slice(offset, offset + parseInt(pageSize));

      res.json({
        success: true,
        data: {
          items,
          total,
          page: parseInt(page),
          pageSize: parseInt(pageSize)
        }
      });
    }
  } catch (error) {
    console.error('获取充值记录错误:', error);
    res.status(500).json({
      success: false,
      message: '获取充值记录失败: ' + error.message
    });
  }
});

// 启动服务器
async function startServer() {
  try {
    // 尝试连接数据库
    const dbConnected = await connectDatabase();

    const server = app.listen(PORT, () => {
      console.log(`一杆云枢服务器运行在 http://localhost:${PORT}`);
      console.log(`API地址: http://localhost:${PORT}/api`);
      console.log(`数据库模式: ${dbConnected ? 'MySQL' : '内存数据库'}`);
    });

    // 404处理 - 必须放在所有路由定义之后
    app.use((req, res) => {
      res.status(404).json({
        success: false,
        message: '接口不存在'
      });
    });

    // 优雅关闭
    process.on('SIGTERM', () => {
      console.log('正在关闭服务器...');
      if (db) db.end();
      server.close(() => {
        console.log('服务器已关闭');
        process.exit(0);
      });
    });

    process.on('SIGINT', () => {
      console.log('正在关闭服务器...');
      if (db) db.end();
      server.close(() => {
        console.log('✅ 服务器已关闭');
        process.exit(0);
      });
    });

  } catch (error) {
    console.error('❌ 启动失败:', error.message);
    process.exit(1);
  }
}

console.log('🔄 服务器启动中...');
startServer();
